set hive.exec.dynamic.partition=true;          --动态分区
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=20;        --每天生成 20 个分区
set hive.exec.max.dynamic.partitions.pernode=20;--每天生成 20 个分区


with t1 as(
select id
     id
     ,name
     ,cn_name
     ,en_name
     ,country_id
     ,is_enable
     ,is_delete
     ,create_by
     ,update_by
     ,create_by_name
     ,update_by_name
     ,create_time
     ,update_time
     ,version
     ,sort
     ,code
     ,proxy_network_code
     ,proxy_network_name
     ,head_ids
     ,head_names
     ,regional_id
     ,is_lowest
     ,parent_code
     ,parent_name
     ,tree_path
     ,length(tree_path)-length(replace(tree_path,'/',''))-2 as level
     ,split(tree_path,'/')[1] as area_id
     ,nvl(split(tree_path,'/')[2],'abc') as first_id
     ,nvl(split(tree_path,'/')[3],'abc') as second_id
     ,nvl(split(tree_path,'/')[4],'abc') as third_id
     ,head_staff_id
     ,head_staff_code
     ,head_staff_name
     from jms_ods.sys_area_info
     where dt='{{ execution_date | cst_ds }}'
     ),
t2 as(
select id,code,head_staff_id,head_staff_code,head_staff_name from
jms_ods.sys_area_info
where dt='{{ execution_date | cst_ds }}'
and (parent_code is  null or parent_code ='')
and is_lowest = 2
),
t3 as(
select id,code,head_staff_id,head_staff_code,head_staff_name from
jms_ods.sys_area_info
where dt='{{ execution_date | cst_ds }}'
),
t4 as(
select id,code,head_staff_id,head_staff_code,head_staff_name from
jms_ods.sys_area_info
where dt='{{ execution_date | cst_ds }}'
),
t5 as(
select id,code,head_staff_id,head_staff_code,head_staff_name from
jms_ods.sys_area_info
where dt='{{ execution_date | cst_ds }}'
)
insert overwrite table jms_dim.`dim_sys_area_info`
select
      t1.id
     ,t1.name
     ,t1.cn_name
     ,t1.en_name
     ,t1.country_id
     ,t1.is_enable
     ,t1.is_delete
     ,t1.create_by
     ,t1.update_by
     ,t1.create_by_name
     ,t1.update_by_name
     ,t1.create_time
     ,t1.update_time
     ,t1.version
     ,t1.sort
     ,t1.code
     ,t1.proxy_network_code
     ,t1.proxy_network_name
     ,t1.head_ids
     ,t1.head_names
     ,t1.regional_id
     ,t1.is_lowest
     ,t1.parent_code
     ,t1.parent_name
     ,t1.tree_path
     ,t1.level as level
     ,t2.id as area_id
     ,t3.id as first_id
     ,t4.id as second_id
     ,t5.id as third_id
     ,t2.code as area_code
     ,t3.code as first_code
     ,t4.code as second_code
     ,t5.code as third_code
     ,t2.head_staff_id as area_head_ids
     ,t2.head_staff_name as area_head_names
     ,t2.head_staff_code as area_head_codes
     ,t3.head_staff_id as first_head_ids
     ,t3.head_staff_name as first_head_names
     ,t3.head_staff_code as first_head_codes
     ,t4.head_staff_id as second_head_ids
     ,t4.head_staff_name as second_head_names
     ,t4.head_staff_code as second_head_codes
     ,t5.head_staff_id as third_head_ids
     ,t5.head_staff_name as third_head_names
     ,t5.head_staff_code as third_head_codes
     ,null as extend1
     ,null as extend2
     ,t1.head_staff_id
     ,t1.head_staff_code
     ,t1.head_staff_name
from t1
join t2
on   t1.area_id= t2.id
left join t3
on   t1.first_id=t3.id
left join t4
on   t1.second_id=t4.id
left join t5
on   t1.third_id=t5.id